package com.zx.oa.dao.census;

import com.alibaba.fastjson.JSONArray;
import com.zx.oa.dao.BaseDao;
import com.zx.oa.entity.ClassInfo;
import com.zx.oa.entity.Major;
import com.zx.oa.entity.Student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CensusDaolpml extends BaseDao implements CensusDao{
    Connection conn=null;
    PreparedStatement pstmt=null;
    ResultSet rs=null;

    @Override
    public List<Student> getStudentList(String major, String className,int currentPageNo,int pageSize,int censusMoringWeek) {
        List<Student> studentList=new ArrayList<>();
        StringBuffer sb=new StringBuffer("SELECT s.`studentId`,m.`major`,c.`className`,s.`studentName` FROM ztestudent s\n" +
                "INNER JOIN zteclassinfo c ON s.`classId`=c.`classId`\n" +
                "INNER JOIN ztemajor m ON c.`studyType`=m.`id`\n" +
                "LEFT JOIN zteresult r ON r.studentid=s.`studentId`\n" +
                "WHERE 1=1\n" +
                "AND s.`studentId` NOT IN  (\n" +
                "SELECT r1.`studentid` FROM zteresult r1 WHERE r1.`examType` = ? \n" +
                ")");
        conn = this.getConnection();
        List<Object> params=new ArrayList<>();
        params.add(censusMoringWeek);
        if (null!=major&&!"".equals(major)){
            sb.append(" AND m.`major`=?");
            params.add(major);
        }
        if (null!=className&&!"".equals(className)){
            sb.append(" AND c.`className`=?");
            params.add(className);
        }
        sb.append(" LIMIT ?,? ");
        params.add((currentPageNo-1)*pageSize);
        params.add(pageSize);
        try {
            pstmt=conn.prepareStatement(sb.toString());
            for (int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs = pstmt.executeQuery();
            while (rs.next()){
                int studentId=rs.getInt("studentId");
                String major1=rs.getString("major");
                String className1=rs.getString("className");
                String studentName1=rs.getString("studentName");

                Student student=new Student(studentId,studentName1,className1,major1);
                studentList.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return studentList;
    }

    @Override
    public int getCensusTotalCount(String major, String className) {
        int count=-1;
        StringBuffer sb=new StringBuffer("SELECT COUNT(*) FROM ztestudent s\n" +
                "INNER JOIN zteclassinfo c ON s.`classId`=c.`classId`\n" +
                "INNER JOIN ztemajor m ON c.`studyType`=m.`id`\n" +
                "LEFT JOIN zteresult r ON r.studentid=s.`studentId`\n" +
                "WHERE 1=1\n" +
                "AND r.examType IS NULL");
        conn = this.getConnection();
        List<Object> params=new ArrayList<>();
        if (null!=major&&!"".equals(major)){
            sb.append(" AND m.`major`=?");
            params.add(major);
        }
        if (null!=className&&!"".equals(className)){
            sb.append(" AND c.`className`=?");
            params.add(className);
        }
        try {
            pstmt = conn.prepareStatement(sb.toString());
            for (int i=0;i<params.size();i++){
                pstmt.setObject((i+1),params.get(i));
            }
            rs=pstmt.executeQuery();
            while (rs.next()){
                count=rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return count;
    }

    @Override
    public List<Major> getMajorAll() {
        List<Major> majorList=new ArrayList<>();
        String sql="SELECT id,major FROM ztemajor";
        conn= this.getConnection();
        try {
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while (rs.next()){
                int id= rs.getInt("id");
                String major=  rs.getString("major");
                Major m=new Major(id,major);
                majorList.add(m);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return majorList;
    }

    @Override
    public List<ClassInfo> getClassAll(String majorName) {
        List<ClassInfo> classInfoList=new ArrayList<>();
        StringBuffer sb=new StringBuffer("SELECT classId,className,studyType FROM zteclassinfo c \n" +
                "INNER JOIN ztemajor m ON m.`id`=c.`studyType`");
        if (null!=majorName||!"".equals(majorName)){
            sb.append(" WHERE m.`major` = ? ");
        }
        conn= this.getConnection();
        try {
            pstmt=conn.prepareStatement(sb.toString());
            if (null!=majorName||!"".equals(majorName)){
                pstmt.setObject(1,majorName);
            }
            rs=pstmt.executeQuery();
            while (rs.next()){
                int classId= rs.getInt("classId");
                String className=  rs.getString("className");
                int  studyType=  rs.getInt("studyType");
                ClassInfo classInfo=new ClassInfo(classId,className,studyType);
                classInfoList.add(classInfo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            this.closeAll(conn,pstmt,rs);
        }
        return classInfoList;
    }

    @Override
    public boolean delete(int id) {
        String sql="DELETE FROM ztestuden|t WHERE studentId=? ";
        Object [] params={id};
        if (this.executeUpdateInfo(sql,params)>0){
            return true;
        }
        return false;
    }

    @Override
    public boolean setResult(String result, String[] nameIds, int census) {
        int count=0;
        for (int i=0;i<nameIds.length;i++){
//            int id;
            int id= Integer.parseInt(nameIds[i]);
            String sql="\n" +
                    "INSERT INTO zteresult (studentid, examdate, examType,studentresult)\n" +
                    "VALUES (?, now() , ?,?)";
            Object [] params={
//                    id=Integer.parseInt(),
                    id,
                    census,
                    result
            };
            if (this.executeUpdateInfo(sql,params)>0){
                count+=1;
            }
        }
        if (count==nameIds.length){
            return true;
        }else {
            return false;
        }

    }
}
